package com.hdoit.ares.logical.brokerage.dao.impl;

import java.util.List;

import org.springframework.stereotype.Component;
import org.springframework.util.StringUtils;

import com.hdoit.ares.logical.brokerage.dao.IShareBrokerageDao;
import com.hdoit.ares.logical.brokerage.model.BrokerageRecord;
import com.hdoit.ares.logical.brokerage.model.ShareBrokerage;
import com.hdoit.ares.logical.cust.model.Customer;
import com.hdoit.ares.logical.insu.model.Insurance;
import com.hdoit.ares.logical.viewmodel.brokerage.ShareBrokerageSearch;
import com.hdoit.ares.logical.viewmodel.cust.CustomerSearch;
import com.tennetcn.common.dao.base.IFinderExpression;
import com.tennetcn.common.dao.base.impl.SuperDao;
import com.tennetcn.common.enums.OrderEnum;
import com.tennetcn.common.message.DaoBaseException;
import com.tennetcn.common.message.PagerModel;
import com.tennetcn.common.utils.CommonUtils;
import com.tennetcn.common.utils.FinderExpressionFactory;
/**
 * @author      yujinhui
 * @email       
 * @createtime  2017年12月01日 15:25:45
 * @comment
 */
@Component
public class ShareBrokerageDaoImpl extends SuperDao<ShareBrokerage> implements IShareBrokerageDao{

	@Override
	public List<ShareBrokerage> queryList(ShareBrokerageSearch search, PagerModel pagerModel) {
		IFinderExpression finderExpression=FinderExpressionFactory.createExpression();
		
		finderExpression.select("shareBrokerage.*",
								"brokerageRecord.hesitateDate as hesitateDay",
								"brokerageRecord.brokerage as money",
								"insurance.insuranceName as productName",
								"brokerageRecord.taxPercent as shareMoney",
								"brokerageRecord.proportion as proportion",
								"customer.username as customerName",
								"customer.level as customerLevel",
								"customer.userIdentity as userIdentity",
								"customer.refereeIdentity as refereeIdentity",
								"brokerageRecord.policyNo as policyNo",
								"brokerageRecord.orderSn as orderSn")
		                .from(ShareBrokerage.class,"shareBrokerage")
		                .leftJoin(Customer.class, "customer")
		                .on("customer.id","shareBrokerage.customerId")
		                .leftJoin(BrokerageRecord.class, "brokerageRecord")
		                .on("brokerageRecord.id","shareBrokerage.brokerageId")
		                .leftJoin(Insurance.class, "insurance")
		                .on("insurance.id","brokerageRecord.productId")
		                .addOrder("shareBrokerage.groupNum", OrderEnum.desc)
		                .addOrder("shareBrokerage.sort", OrderEnum.asc)
		                .setMainTableAlias("shareBrokerage");
		
		appendFinder(search,finderExpression);

        if(pagerModel == null){
            return queryList(finderExpression, search); 
          }
        return queryList(finderExpression, search, pagerModel);
	}

	private void appendFinder(ShareBrokerageSearch search, IFinderExpression finder) {
		IFinderExpression finderExpression=FinderExpressionFactory.createExpression();
		
		finderExpression.select("bro.id")
			  			.from(BrokerageRecord.class,"bro")
			  			.leftJoin(Customer.class,"cus")
			  			.on("bro.userId","cus.id");
		
		if(!StringUtils.isEmpty(search.getConfirmState())){
			finder.andWhere("shareBrokerage.confirmState like #{confirmState}")
				  .setParam("confirmState", "%"+search.getConfirmState()+"%");
		}
	
		if(!StringUtils.isEmpty(search.getCustomerName())){
			
			finderExpression.andWhere("cus.username like #{username}");
							
			finder.andWhere("brokerageRecord.id in("+finderExpression.toSql()+")")
				  .setParam("username", "%"+search.getCustomerName()+"%");
			
		}
		
		if(search.getStartMoney()!= null){
			finder.andWhere("brokerageRecord.brokerage >= #{startMoney}")
				  .setParam("startMoney", search.getStartMoney());
		}
		if(search.getEndMoney()!= null){
			finder.andWhere("brokerageRecord.brokerage <= #{endMoney}")
				  .setParam("endMoney", search.getEndMoney());
		}
		
		if(search.getStartDate()!=null){
			finder.andWhere("TIMESTAMPDIFF(SECOND,#{startDate},shareBrokerage.generateDate)>=0")
				  .setParam("startDate", search.getStartDate());
		}
		if(search.getEndDate()!=null){
			finder.andWhere("TIMESTAMPDIFF(SECOND,#{endDate},shareBrokerage.generateDate)<=0")
				  .setParam("endDate", search.getEndDate());
		}
		
		if(search.getUserIdentity() != null){
			
			finderExpression.andWhere("cus.userIdentity = #{userIdentity}");
			
			finder.andWhere("brokerageRecord.id in("+finderExpression.toSql()+")")
			  	  .setParam("userIdentity", search.getUserIdentity());
		}
		
		if(!StringUtils.isEmpty(search.getOrderSn())){
			finder.andWhere("brokerageRecord.orderSn  like #{orderSn}")
				  .setParam("orderSn", "%"+search.getOrderSn()+"%");
		}
		
		if(!StringUtils.isEmpty(search.getPolicyNo())){
			finder.andWhere("brokerageRecord.policyNo like #{policyNo}")
				  .setParam("policyNo", "%"+search.getPolicyNo()+"%");
		}
		
		if(!StringUtils.isEmpty(search.getCityId())){
			finder.andWhere("customer.cityId = #{cityId}")
			  	  .setParam("cityId", search.getCityId());
		}
		
		if(!StringUtils.isEmpty(search.getRefereeId())){
			IFinderExpression highMakerExpression=FinderExpressionFactory.createExpression();
			
			highMakerExpression.select("sh.brokerageId")
				  			   .from(ShareBrokerage.class,"sh")
				  			   .andWhere("sh.customerId = #{refereeId}");
			
			finder.andWhere("shareBrokerage.brokerageId in (" + highMakerExpression.toSql() + ")" )
				  .setParam("refereeId", search.getRefereeId());
		}
	
	}

	@Override
	public List<ShareBrokerage> queryList(ShareBrokerageSearch search) {
		return queryList(search,null);
	}

	@Override
	public int queryCount(ShareBrokerageSearch search) {
		IFinderExpression finderExpression=FinderExpressionFactory.createExpression();
		
		finderExpression.select("count(DISTINCT shareBrokerage.id) as count")
        				.from(ShareBrokerage.class,"shareBrokerage")
        				.leftJoin(Customer.class, "customer")
        				.on("customer.id","shareBrokerage.customerId")
        				.leftJoin(BrokerageRecord.class, "brokerageRecord")
        				.on("brokerageRecord.id","shareBrokerage.brokerageId")
        				.leftJoin(Insurance.class, "insurance")
		                .on("insurance.id","brokerageRecord.productId")
        				.setMainTableAlias("shareBrokerage");
		
		appendFinder(search,finderExpression);
		
		return queryCount(finderExpression, search);
	}

	@Override
	public int queryMaxGroupNum() {
		IFinderExpression finderExpression=FinderExpressionFactory.createExpression();
		
		finderExpression.select("max(groupNum) as m")
						.from(ShareBrokerage.class);
		
		String scalar=queryScalar(finderExpression);
		
		if(StringUtils.isEmpty(scalar)){
			return 1;
		}
		
		return CommonUtils.getNumber(scalar) + 1;
	}

	@Override
	public List<ShareBrokerage> queryListByBrokerageId(String brokerageId) {
        IFinderExpression finderExpression=FinderExpressionFactory.createExpression();
		
		finderExpression.select("shareBrokerage.*","brokerageRecord.hesitateDate as hesitateDay","brokerageRecord.brokerage as money","insurance.insuranceName as productName","brokerageRecord.taxPercent as shareMoney","brokerageRecord.proportion as proportion","customer.username as customerName","customer.level as customerLevel","customer.userIdentity as userIdentity","customer.refereeIdentity as refereeIdentity")
		                .from(ShareBrokerage.class,"shareBrokerage")
		                .leftJoin(Customer.class, "customer")
        				.on("customer.id","shareBrokerage.customerId")
		                .leftJoin(BrokerageRecord.class, "brokerageRecord")
		                .on("brokerageRecord.id","shareBrokerage.brokerageId")
		                .leftJoin(Insurance.class, "insurance")
		                .on("insurance.id","brokerageRecord.productId")
		                .andWhere("shareBrokerage.brokerageId = #{brokerageId}")
		                .setParam("brokerageId", brokerageId)
		                .addOrder("shareBrokerage.sort", OrderEnum.asc)
		                .setMainTableAlias("shareBrokerage");
		
		return queryList(finderExpression);
	}
	
	@Override
	public ShareBrokerage queryModel(String key) throws DaoBaseException {
        IFinderExpression finderExpression=FinderExpressionFactory.createExpression();
		
		finderExpression.select("shareBrokerage.*","brokerageRecord.hesitateDate as hesitateDay","brokerageRecord.brokerage as money","insurance.insuranceName as productName","brokerageRecord.taxPercent as shareMoney","brokerageRecord.proportion as proportion","customer.username as customerName","customer.level as customerLevel","customer.userIdentity as userIdentity","customer.refereeIdentity as refereeIdentity")
		                .from(ShareBrokerage.class,"shareBrokerage")
		                .leftJoin(Customer.class, "customer")
		                .on("customer.id","shareBrokerage.customerId")
		                .leftJoin(BrokerageRecord.class, "brokerageRecord")
		                .on("brokerageRecord.id","shareBrokerage.brokerageId")
		                .leftJoin(Insurance.class, "insurance")
		                .on("insurance.id","brokerageRecord.productId")
		                .addOrder("shareBrokerage.groupNum", OrderEnum.desc)
		                .addOrder("shareBrokerage.sort", OrderEnum.asc)
		                .setMainTableAlias("shareBrokerage")
		                .andWhere("shareBrokerage.id = #{key}")
		                .setParam("key", key);
		
		return super.queryModel(finderExpression);
	}

	@Override
	public List<ShareBrokerage> queryShareBrokerageForStatistic(CustomerSearch search) {
		IFinderExpression finderExpression=FinderExpressionFactory.createExpression();
		
		finderExpression.select("shareBrokerage.*","brokerageRecord.brokerage as money")
						.from(ShareBrokerage.class,"shareBrokerage")
						.leftJoin(BrokerageRecord.class,"brokerageRecord")
						.on("brokerageRecord.id","shareBrokerage.brokerageId")
						.andWhere("DATE_FORMAT(brokerageRecord.`recordDate`, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )")
						.andWhere("shareBrokerage.customerId = #{customerId}")
						.setParam("customerId", search.getId());
		
		
		return queryList(finderExpression);
	}

}
